package Pearl.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import Pearl.beans.Users;
import Pearl.db.DbConnect;

public class UsersDaoImp implements UsersDao {


	private static final Logger log = Logger.getLogger(AddressDaoImp.class);
	
	public ArrayList<Users> rechUsers() throws SQLException{
		try{
			CallableStatement callst = null;
			ResultSet rs = null;
			ArrayList<Users> us = new ArrayList<Users>();
			
			callst = DbConnect.getInstance().prepareCall("call rechUsers()");
			log.debug("chargement : OK");
			rs = callst.executeQuery();
			while(rs.next()){
				Users myUsers = garnirUsers(rs);
				us.add(myUsers);
			}
			log.debug("execution rech users : OK");
			return us;
		}
		catch(SQLException sqlex){
			log.error("Execution sup users : OK ");
			return null;
	}
	}
	
	public void ajoutUsers(Users addUsers) throws SQLException{
		
		try{
			CallableStatement callst = null;
			callst = DbConnect.getInstance().prepareCall("Call insert_users(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		
			callst.setString(1, addUsers.getType());
			callst.setString(2, addUsers.getName());
			callst.setString(3, addUsers.getfName());
			callst.setString(4, addUsers.getNickname());
			callst.setString(5, addUsers.getPhone());
			callst.setInt(6, addUsers.getId_address());
			callst.setInt(7, addUsers.getId_address_liv());
			callst.setString(8, addUsers.getIp());
			callst.setString(9, addUsers.getEmail());
			callst.setString(10, addUsers.getPwd());
			callst.setInt(11, addUsers.getFlag());
			callst.setDate(12, addUsers.getRegtime());
			callst.setString(13, addUsers.getNewsletter());
			callst.setInt(14, addUsers.getId_role());
			
			callst.executeQuery();
			
			log.debug("execution insert users : OK");
			
		}
		catch(SQLException sqlex){
			log.error("Execution sup users : OK ");
			return;
		
	}
	}
	
	public void modifUsers(Users modifUsers) throws SQLException{
		try{
			CallableStatement callst = null;
			ResultSet rs = null;
			ArrayList<Users> us = new ArrayList<Users>();
			callst = DbConnect.getInstance().prepareCall("Call modifUsers(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			log.debug("chargement : OK ");
			
			callst.setString(1, modifUsers.getType());
			callst.setString(2, modifUsers.getName());
			callst.setString(3, modifUsers.getfName());
			callst.setString(4, modifUsers.getNickname());
			callst.setString(5, modifUsers.getPhone());
			callst.setInt(6, modifUsers.getId_address());
			callst.setInt(7, modifUsers.getId_address_liv());
			callst.setString(8, modifUsers.getIp());
			callst.setString(9, modifUsers.getEmail());
			callst.setString(10, modifUsers.getPwd());
			callst.setInt(11, modifUsers.getFlag());
			callst.setDate(12, modifUsers.getRegtime());
			callst.setString(13, modifUsers.getNewsletter());
			callst.setInt(14, modifUsers.getId_role());
			
			rs=callst.executeQuery();
			
			log.debug("Exe modif : OK");
			
			while(rs.next()){
				Users myUsers = garnirUsers(rs);
				us.add(myUsers);
			}
	}
		catch(SQLException sqlex){
			log.error("Execution sup users : OK ");
			return;
		}
	}
	
	
	
	
	public void modifAdmin(int id_users) throws SQLException{
		try{
			CallableStatement callst = null;
			ResultSet rs = null;
			ArrayList<Users> us = new ArrayList<Users>();
			callst = DbConnect.getInstance().prepareCall("Call modifAdmin(?)");
			log.debug("chargement : OK ");
			callst.setInt(1, id_users);
			rs = callst.executeQuery();
			while(rs.next()){
				Users myUsers = garnirUsers(rs);
				us.add(myUsers);
			}
		}
			catch(SQLException sqlex){
				log.error("Execution sup users : OK ");
				return;
			}
	}
	
	public void suppUsers(int id_users) throws SQLException{
		try{
			CallableStatement callst = null;
			ResultSet rs = null;
			ArrayList<Users> us = new ArrayList<Users>();
			
			callst = DbConnect.getInstance().prepareCall("Call supUsers(?)");
			log.debug("chargement : OK ");
			callst.setInt(1, id_users);
			rs = callst.executeQuery();
					while(rs.next()){
						Users myUsers = garnirUsers(rs);
						us.add(myUsers);
					}
		}
		catch(SQLException sqlex){
			log.error("Execution sup users : OK ");
			return;
		}
	}
	
	public boolean VerifUsers(String email, String pwd){
		boolean existe = false;
		
		try{
			CallableStatement callst = null;
			ResultSet rs = null;
			Users myUsers =new Users();
			callst = DbConnect.getInstance().prepareCall("Call validUsers(?,?)");
			log.debug("chargement : OK ");
			
			callst.setString(1, myUsers.getEmail());
			callst.setString(2, myUsers.getPwd());
			
			rs=callst.executeQuery();
			log.debug("proc valid users : OK");
			if(rs!= null){
				existe=true;
			}
			
		}
		catch(SQLException e){
			log.error("proc valid users : NO");
			e.printStackTrace();
		}
		return existe;
	}
	
	public Users garnirUsers(ResultSet rs) throws SQLException{
		
		Users users2 =new Users();
		users2.setId_users(rs.getInt("id_Users"));
		users2.setType(rs.getString("type"));
		users2.setName(rs.getString("name"));
		users2.setfName(rs.getString("fName"));
		users2.setNickname(rs.getString("nickname"));
		users2.setPhone(rs.getString("phone"));
		users2.setId_address(rs.getInt("id_address"));
		users2.setId_address_liv(rs.getInt("id_address_liv"));
		users2.setIp(rs.getString("ip"));
		users2.setEmail(rs.getNString("email"));
		users2.setPwd(rs.getString("pwd"));
		users2.setFlag(rs.getInt("flag"));
		users2.setRegtime(rs.getDate("regtime"));
		users2.setNewsletter(rs.getString("newsletter"));
		users2.setId_role(rs.getInt("id_role"));
		
		log.debug("Garnir l'users");
		
		return users2;
	}

	@Override
	public Boolean verifUsers(String email, String pwd) {
		// TODO Auto-generated method stub
		return null;
	}
}
